﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Windows.Forms;

namespace Pufang.Execl.BusinessCompute.Operation
{
    public class Class1
    {

        private DataTable SetImportDateTable(Hashtable ht, Excel._Worksheet mySheet,ComboBox cmbSelectColumn)
        {
            DataTable dt = new DataTable();
            //添加过滤条件
            cmbSelectColumn.Items.Clear();
            foreach (string key in ht.Keys)
            {
                DataColumn dc = new DataColumn();
                string name = mySheet.get_Range(key, Type.Missing).Value + string.Empty;
                dc.ColumnName = key;
                dc.Caption = name;
                dc.DataType = mySheet.get_Range(GetExeclNextColnumName(key), Type.Missing).Value.GetType();
                dt.Columns.Add(dc);
                cmbSelectColumn.Items.Add(new Province(key, name));

            }
            cmbSelectColumn.SelectedIndex = 0;
            return dt;
        }

        private void ImportExcelDate(string path, Hashtable ht, string passWork, ComboBox cmbSelectColumn, DataGridView dgrSourceDate)
        {

            //不为空字段
            string isnotNullName = string.Empty;
            foreach (string key in ht.Keys)
            {
                if (ht[key] + string.Empty == true.ToString())
                {
                    isnotNullName = key;
                    break;
                }
            }

            if (isnotNullName == string.Empty)
            {
                MessageBox.Show("配置文件错误。 没有一列为非空,请查询制作配置文件。");
                return;
            }

            Excel.ApplicationClass myExcel = new Excel.ApplicationClass();
            Excel._Workbook myBook = null;
            Excel._Worksheet mySheet = null;
            myExcel.Workbooks.Open(path,
                Type.Missing, Type.Missing, Type.Missing, (string.IsNullOrEmpty(passWork) == true ? Type.Missing : passWork), Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            myExcel.DisplayAlerts = false;
            myExcel.Visible = false;
            myBook = myExcel.Workbooks[1];
            myBook.Activate();
            mySheet = (Excel._Worksheet)myBook.Worksheets[1];
            mySheet.Activate();

            //字母位数
            int count = 0;
            foreach (char cha in isnotNullName)
            {

                if (Convert.ToInt32(cha) >= 48 && Convert.ToInt32(cha) <= 59)
                {
                    break;
                }
                count++;
            }

            //所在行
            int colunmIndex = Convert.ToInt32(isnotNullName.Remove(0, count));
            //所在列字母
            string colunmName = isnotNullName.Replace(colunmIndex.ToString(), "");


            //根据配置文件获取结构
            DataTable dt = SetImportDateTable(ht, mySheet, cmbSelectColumn);

            //读取标题下面的值
            colunmIndex++;
            while ((mySheet.get_Range(colunmName + colunmIndex).Value + "").ToString() != "")
            {
                DataRow dr = dt.NewRow();
                foreach (string key in ht.Keys)
                {
                    string keyName = GetExeclColnumName(key);
                    dr[key] = mySheet.get_Range(keyName + colunmIndex, Type.Missing).Value;
                }
                dt.Rows.Add(dr);
                colunmIndex++;

            }
            myBook.Close(false, Type.Missing, Type.Missing);
            myExcel.Quit();

            dgrSourceDate.DataSource = dt;
            foreach (DataGridViewColumn item in dgrSourceDate.Columns)
            {
                SetDataGridViewColumn(item.HeaderText, this.dgrSourceDate);
            }
        

        }
 

        private string GetExeclNextColnumName(string cellName)
        {
            //字母位数
            int count = 0;
            foreach (char cha in cellName)
            {

                if (Convert.ToInt32(cha) >= 48 && Convert.ToInt32(cha) <= 59)
                {
                    break;
                }
                count++;
            }


            //所在行
            int colunmIndex = Convert.ToInt32(cellName.Remove(0, count));
            //所在列字母
            string colunmName = cellName.Replace(colunmIndex.ToString(), "");

            return colunmName + (colunmIndex + 1);

        }

        /// <summary>
        /// 获取列名
        /// </summary>
        /// <returns></returns>
        private string GetExeclColnumName(string cellName)
        {
            //字母位数
            int count = 0;
            foreach (char cha in cellName)
            {

                if (Convert.ToInt32(cha) >= 48 && Convert.ToInt32(cha) <= 59)
                {
                    break;
                }
                count++;
            }


            //所在行
            int colunmIndex = Convert.ToInt32(cellName.Remove(0, count));
            //所在列字母
            string colunmName = cellName.Replace(colunmIndex.ToString(), "");

            return colunmName;

        }

    }

    class Province
    {

        //编写构造函数

        public Province(string id, string name)
        {

            this.Id = id;

            this.Name = name;

        }

        public string Id
        {

            get;

            set;

        }



        public string Name
        {

            get;

            set;

        }

        public override string ToString()
        {

            return this.Name;

        }

    }
}
